package org.xt.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Random;
import java.util.Set;

import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.util.NutMap;
import org.nutz.trans.Atom;
import org.nutz.trans.Trans;
import org.xt.constants.Res;
import org.xt.entity.BaseTable;
import org.xt.entity.DataDetailsAuto;
import org.xt.entity.DataDetailsHM;
import org.xt.entity.DataSummary;
import org.xt.entity.DataGJEHState;
import org.xt.entity.DataItem;
import org.xt.entity.DataMachine;
import org.xt.entity.DataTempputState;
import org.xt.entity.Permission;
import org.xt.entity.User;
/**
 * 钢架E化数据服务类
 * @author XiangTao
 */
@IocBean(name="dataSummaryService")
public class DataSummaryService {
	@Inject
	private Dao dao;
	@Inject
	private PermissionService permissionService;
	@Inject
	private UserService userService;
	
	/**
	 * 根据机种ID获取数据库数据，然后转换为级联数据列表，包括样式
	 * @param machineId
	 * @return
	 */
	public NutMap list(int machineId, String table){
		DataMachine dm = dao.fetch(DataMachine.class, machineId);
		if(dm != null){
			NutMap nm =NutMap.NEW().addv("name", dm.getName()); //添加机种
			nm.addv("style", "background-color: #9bd8d9;color:#333;");
			List<DataSummary> items = dao.query(DataSummary.class
					, Cnd.where("dataMachineId", "=", machineId).groupBy("dataItemId"), null, "dataItemId");
			if(items != null && items.size() > 0){
				
				List<NutMap> dataItem = new ArrayList<NutMap>();
				
				for(int i = 0; i < items.size(); i ++){
					DataItem item = dao.fetch(DataItem.class, items.get(i).getDataItemId());
					NutMap nmItem = NutMap.NEW().addv("name", item.getName()); //添加项目
					nmItem.addv("style", "background-color: #92CDDC;color:#333;");
					dataItem.add(nmItem);
					List<DataSummary> gjehStates = dao.query(DataSummary.class
							, Cnd.where("dataMachineId", "=", machineId)
							.and("dataItemId", "=", items.get(i).getDataItemId())
							.groupBy("dataGJEHStateId"), null, "dataGJEHStateId");
					
					if(gjehStates!= null && gjehStates.size() > 0){
						List<NutMap> dataGjeh = new ArrayList<NutMap>();
						
						for(int j = 0; j < gjehStates.size(); j ++){
							DataGJEHState gjehState = dao.fetch(DataGJEHState.class, gjehStates.get(j).getDataGJEHStateId());
							NutMap nmGjehState = NutMap.NEW().addv("name", gjehState.getName());
							
							nmGjehState.addv("style", "background: #e0b5bf;color:#333;font-weight:bold;");
							dataGjeh.add(nmGjehState);
							List<DataSummary> tempputStates = dao.query(DataSummary.class
									, Cnd.where("dataMachineId", "=", machineId)
									.and("dataItemId", "=", items.get(i).getDataItemId())
									.and("dataGJEHStateId", "=", gjehStates.get(j).getDataGJEHStateId())
									.groupBy("dataTempputStateId"), null, "dataTempputStateId");
							if(tempputStates != null && tempputStates.size() > 0){
								List<NutMap> dataTempputState = new ArrayList<NutMap>();
								int totalSum = 0;
								for(int k = 0; k < tempputStates.size(); k ++){
									DataTempputState tempputState = dao.fetch(DataTempputState.class, tempputStates.get(k).getDataTempputStateId());
									NutMap nmTempputState = NutMap.NEW().addv("name", tempputState.getName()); //添加项目
									nmTempputState.addv("style", "background: #c8b9d6;color:#333;font-weight:bold;");
									
									dataTempputState.add(nmTempputState);
									DataSummary data1Fgjeh = dao.fetchx(DataSummary.class
											, machineId
											, items.get(i).getDataItemId()
											, gjehStates.get(j).getDataGJEHStateId()
											, tempputStates.get(k).getDataTempputStateId());
									//统计
									data1Fgjeh.setNum(tongji(data1Fgjeh, table));
									List<NutMap> data = new ArrayList<>();
									
									NutMap nmData = NutMap.NEW().addv("name", data1Fgjeh.getNum());
									nmData.addv("style", "background: #f59393;;color:#333;font-weight:bold;");
									nmData.addv("target", data1Fgjeh);//
									data.add(nmData);
									nmTempputState.addv("data", data);
									totalSum += data1Fgjeh.getNum();
								}
								List<NutMap> nmTotalSum = new ArrayList<>();
								NutMap nmTotal = NutMap.NEW().addv("name", totalSum)
										.addv("data", dataTempputState);
								nmTotal.addv("style", "background: #ffbf5b;color:#333;font-weight:bold;");
								nmTotalSum.add(nmTotal);
								nmGjehState.addv("data", nmTotalSum);
							}
						}
						nmItem.addv("data", dataGjeh);
					}
				}
				nm.addv("data", dataItem);
			}
			return nm;
		}
		return null;
	}
	
	/**
	 * 根据机种ID获取数据库数据，然后转换为级联数据列表，包括样式
	 * @param machineId
	 * @return
	 */
	public NutMap list1(int machineId, String table){
		DataMachine machine = dao.fetch(DataMachine.class, machineId);
		if(machine != null){
			NutMap nm =NutMap.NEW().addv("name", machine.getName()); //添加机种
			nm.addv("style", "background-color: #9bd8d9;color:#333;");
			List<DataItem> items = findItemsByAuto(machine.getName());
			if(items != null && items.size() > 0){
				List<NutMap> dataItem = new ArrayList<NutMap>();
				
				for(int i = 0; i < items.size(); i ++){
					DataItem item = items.get(i);
					int itemCount = dao.count("tb_data_1fgjeh_details_auto"
							, Cnd.where("dataMachine", "=", machine.getName())
							.and("dataItem", "=", item.getName()));
					if(itemCount <= 0){
						continue;
					}
					NutMap nmItem = NutMap.NEW().addv("name", item.getName()); //添加项目
					nmItem.addv("style", "background-color: #92CDDC;color:#333;");
					dataItem.add(nmItem);
					List<DataGJEHState> gjehStates = findGjehStatesByAuto(machine.getName(), item.getName());
					
					if(gjehStates!= null && gjehStates.size() > 0){
						List<NutMap> dataGjeh = new ArrayList<NutMap>();
						
						for(int j = 0; j < gjehStates.size(); j ++){
							DataGJEHState gjehState = gjehStates.get(j);
							int gjehStateCount = dao.count("tb_data_1fgjeh_details_auto"
									, Cnd.where("dataMachine", "=", machine.getName())
									.and("dataItem", "=", item.getName())
									.and("dataGJEHState", "=", gjehState.getName()));
							if(gjehStateCount <= 0){
								continue;
							}
							
							NutMap nmGjehState = NutMap.NEW().addv("name", gjehState.getName());
							
							nmGjehState.addv("style", "background: #e0b5bf;color:#333;font-weight:bold;");
							dataGjeh.add(nmGjehState);
							List<DataTempputState> tempputStates = findTempputesByAuto(machine.getName(), item.getName(), gjehState.getName());
							if(tempputStates != null && tempputStates.size() > 0){
								List<NutMap> dataTempputState = new ArrayList<NutMap>();
								int CUM = 0;
								for(int k = 0; k < tempputStates.size(); k ++){
									DataTempputState tempputState = tempputStates.get(k);
									int tempputeStateCount = dao.count("tb_data_1fgjeh_details_auto"
											, Cnd.where("dataMachine", "=", machine.getName())
											.and("dataItem", "=", item.getName())
											.and("dataGJEHState", "=", gjehState.getName())
											.and("dataTempputState", "=", tempputState.getName()));
									if(tempputeStateCount <= 0){
										continue;
									}
									
									NutMap nmTempputState = NutMap.NEW().addv("name", tempputState.getName()); //添加项目
									nmTempputState.addv("style", "background: #c8b9d6;color:#333;font-weight:bold;");
									
									dataTempputState.add(nmTempputState);
									String strMachine = machine.getName();
									String strItem = item.getName();
									String strGjehState = gjehState.getName();
									String strTemppute = tempputState.getName();
									
									List<String> liaohaos = findLiaohao(strMachine, strItem, strGjehState, strTemppute);
									List<NutMap> nmLiaohaos = new ArrayList<>();
									if(liaohaos == null || liaohaos.size() == 0){
										NutMap nmLiaohao = NutMap.NEW().addv("name", " ");
										nmLiaohao.addv("style", "background-color: #92CDDC;color:#333;");
										nmLiaohao.addv("stopCascade", "true");
										List<NutMap> nmDefaults = new ArrayList<>();
										for(int h = 0; h < 7; h ++){
											NutMap nmDefault = NutMap.NEW();
											nmDefault.addv("name", " ");
											if(h == 0 || h == 5 || h == 6){
												nmDefault.addv("style", "background: #92CDDC;color:#333;font-weight:bold;");
											}else if(h == 1 || h == 2){
												nmDefault.addv("style", "background: #e0b5bf;color:#333;font-weight:bold;");
											}else{
												nmDefault.addv("style", "background: #c8b9d6;color:#333;font-weight:bold;");
											}
											nmDefaults.add(nmDefault);
										}
										nmLiaohao.addv("data", nmDefaults);
										nmLiaohaos.add(nmLiaohao);
										
										List<NutMap> dataTempputesSum = new ArrayList<>();
										NutMap nmTempputeSum = NutMap.NEW().addv("name", 0);
										nmTempputeSum.addv("data", nmLiaohaos);
										nmTempputeSum.addv("style", "background: #c8b9d6;color:#333;font-weight:bold;");
										dataTempputesSum.add(nmTempputeSum);
										nmTempputState.addv("data", dataTempputesSum);
										CUM = 0;
										continue;
									}
									
									int tempputeNum = 0;
									for(int t = 0; t < liaohaos.size(); t ++){
										NutMap nmLiaohao = NutMap.NEW().addv("name", liaohaos.get(t));
										nmLiaohao.addv("style", "background-color: #92CDDC;color:#333;");
										List<String> gjNos = findGjNo(strMachine, strItem, strGjehState, strTemppute, liaohaos.get(t));
										List<NutMap> nmGjNos = new ArrayList<>();
										int liaohaoNum = 0;
										for(int q = 0; q < gjNos.size(); q ++){
											NutMap nmGjNo = NutMap.NEW().addv("name", gjNos.get(q));
											nmGjNo.addv("style", "background: #e0b5bf;color:#333;font-weight:bold;");
											List<String> dachuweis = findDachuwei(strMachine, strItem, strGjehState, strTemppute, liaohaos.get(t), gjNos.get(q));
											List<NutMap> nmDachuweis = new ArrayList();
											int gjNoNum = 0;
											for(int v = 0; v < dachuweis.size(); v ++){
												NutMap nmDachuwei = NutMap.NEW().addv("name", dachuweis.get(v));
												nmDachuwei.addv("style", "background: #c8b9d6;color:#333;font-weight:bold;");
												List<String> xiaochuweis = findXiaochuwei(strMachine, strItem, strGjehState, strTemppute, liaohaos.get(t), gjNos.get(q), dachuweis.get(v));
												List<NutMap> nmXiaochuweis = new ArrayList<>();
												int dachuweiNum = 0;
												for(int b = 0; b < xiaochuweis.size(); b ++){
													NutMap nmXiaochuwei = NutMap.NEW().addv("name", xiaochuweis.get(b));
													nmXiaochuwei.addv("style", "background-color: #92CDDC;color:#333;");
													int xiaochuwei = tongji(table, strMachine, strItem, strGjehState, strTemppute, liaohaos.get(t), gjNos.get(q), dachuweis.get(v), xiaochuweis.get(b));
													
													List<NutMap> nmXiaochuweiNums = new ArrayList<>();
													NutMap nmXiaochuweiNum = NutMap.NEW().addv("name", xiaochuwei);
													nmXiaochuweiNum.addv("style", "background-color: #92CDDC;color:#333;");
													nmXiaochuweiNums.add(nmXiaochuweiNum);
													nmXiaochuwei.addv("data", nmXiaochuweiNums);
													
													nmXiaochuweis.add(nmXiaochuwei);
													dachuweiNum += xiaochuwei;
												}
												
												List<NutMap> nmDachuweiNums = new ArrayList<>();
												NutMap nmDachuweiNum = NutMap.NEW().addv("name", dachuweiNum);
												nmDachuweiNum.addv("style", "background: #c8b9d6;color:#333;font-weight:bold;");
												nmDachuweiNum.addv("data", nmXiaochuweis);
												nmDachuweiNums.add(nmDachuweiNum);
												nmDachuwei.addv("data", nmDachuweiNums);
												nmDachuweis.add(nmDachuwei);
												gjNoNum += dachuweiNum;
											}
											List<NutMap> nmGjNoNums = new ArrayList<>();
											NutMap nmGjNoNum = NutMap.NEW().addv("name", gjNoNum);
											nmGjNoNum.addv("style", "background: #e0b5bf;color:#333;font-weight:bold;");
											nmGjNoNum.addv("data", nmDachuweis);
											nmGjNoNums.add(nmGjNoNum);
											nmGjNo.addv("data", nmGjNoNums);
											nmGjNos.add(nmGjNo);
											
											liaohaoNum += gjNoNum;
										}
										
										List<NutMap> nmLiaohaoSums = new ArrayList<>();
										NutMap nmLiaohaoSum = NutMap.NEW().addv("name", liaohaoNum);
										nmLiaohaoSum.addv("style", "background-color: #92CDDC;color:#333;");
										nmLiaohaoSum.addv("data", nmGjNos);
										nmLiaohaoSums.add(nmLiaohaoSum);
										nmLiaohao.addv("data", nmLiaohaoSums);
										nmLiaohaos.add(nmLiaohao);
										tempputeNum += liaohaoNum;
									}
									List<NutMap> dataTempputesSum = new ArrayList<>();
									NutMap nmTempputeSum = NutMap.NEW().addv("name", tempputeNum);
									nmTempputeSum.addv("data", nmLiaohaos);
									nmTempputeSum.addv("style", "background: #c8b9d6;color:#333;font-weight:bold;");
									dataTempputesSum.add(nmTempputeSum);
									nmTempputState.addv("data", dataTempputesSum);
									CUM += tempputeNum;
								}
								
								List<NutMap> cums = new ArrayList<>();
								NutMap nmCum = NutMap.NEW().addv("name", CUM)
										.addv("data", dataTempputState);
								nmCum.addv("style", "background: #ffbf5b;color:#333;font-weight:bold;");
								cums.add(nmCum);
								nmGjehState.addv("data", cums);
							}
						}
						nmItem.addv("data", dataGjeh);
					}
				}
				nm.addv("data", dataItem);
			}
			return nm;
		}
		return null;
	}
	/**
	 * 根据机种ID获取数据库数据，然后转换为级联数据列表，包括样式
	 * @param machineId
	 * @return
	 */
	public NutMap list2(String machine, String item, String gjehState, String tempputState
			, String liaohao){
		NutMap nmLiaohao = NutMap.NEW().addv("name", liaohao);
		nmLiaohao.addv("style", "background-color: #92CDDC;color:#333;");
		List<String> gjNos = findGjNo(machine, item, gjehState, tempputState, liaohao);
		List<NutMap> nmGjNos = new ArrayList<>();
		int liaohaoNum = 0;
		if(gjNos == null || gjNos.size() <= 0){
			return null;
		}
		for(int q = 0; q < gjNos.size(); q ++){
			NutMap nmGjNo = NutMap.NEW().addv("name", gjNos.get(q));
			nmGjNo.addv("style", "background: #e0b5bf;color:#333;font-weight:bold;");
			List<String> dachuweis = findDachuwei(machine, item, gjehState, tempputState, liaohao, gjNos.get(q));
			List<NutMap> nmDachuweis = new ArrayList();
			int gjNoNum = 0;
			for(int v = 0; v < dachuweis.size(); v ++){
				NutMap nmDachuwei = NutMap.NEW().addv("name", dachuweis.get(v));
				nmDachuwei.addv("style", "background: #c8b9d6;color:#333;font-weight:bold;");
				List<String> xiaochuweis = findXiaochuwei(machine, item, gjehState, tempputState, liaohao, gjNos.get(q), dachuweis.get(v));
				List<NutMap> nmXiaochuweis = new ArrayList<>();
				int dachuweiNum = 0;
				for(int b = 0; b < xiaochuweis.size(); b ++){
					NutMap nmXiaochuwei = NutMap.NEW().addv("name", xiaochuweis.get(b));
					nmXiaochuwei.addv("style", "background-color: #92CDDC;color:#333;");
					int xiaochuwei = tongji("tb_data_1fgjeh_details_auto", machine, item, gjehState, tempputState, liaohao, gjNos.get(q), dachuweis.get(v), xiaochuweis.get(b));
					
					List<NutMap> nmXiaochuweiNums = new ArrayList<>();
					NutMap nmXiaochuweiNum = NutMap.NEW().addv("name", xiaochuwei);
					nmXiaochuweiNum.addv("style", "background-color: #92CDDC;color:#333;");
					nmXiaochuweiNums.add(nmXiaochuweiNum);
					nmXiaochuwei.addv("data", nmXiaochuweiNums);
					
					nmXiaochuweis.add(nmXiaochuwei);
					dachuweiNum += xiaochuwei;
				}
				
				List<NutMap> nmDachuweiNums = new ArrayList<>();
				NutMap nmDachuweiNum = NutMap.NEW().addv("name", dachuweiNum);
				nmDachuweiNum.addv("style", "background: #c8b9d6;color:#333;font-weight:bold;");
				nmDachuweiNum.addv("data", nmXiaochuweis);
				nmDachuweiNums.add(nmDachuweiNum);
				nmDachuwei.addv("data", nmDachuweiNums);
				nmDachuweis.add(nmDachuwei);
				gjNoNum += dachuweiNum;
			}
			List<NutMap> nmGjNoNums = new ArrayList<>();
			NutMap nmGjNoNum = NutMap.NEW().addv("name", gjNoNum);
			nmGjNoNum.addv("style", "background: #e0b5bf;color:#333;font-weight:bold;");
			nmGjNoNum.addv("data", nmDachuweis);
			nmGjNoNums.add(nmGjNoNum);
			nmGjNo.addv("data", nmGjNoNums);
			nmGjNos.add(nmGjNo);
			
			liaohaoNum += gjNoNum;
		}
		
		List<NutMap> nmLiaohaoSums = new ArrayList<>();
		NutMap nmLiaohaoSum = NutMap.NEW().addv("name", liaohaoNum);
		nmLiaohaoSum.addv("style", "background-color: #92CDDC;color:#333;");
		nmLiaohaoSum.addv("data", nmGjNos);
		nmLiaohaoSums.add(nmLiaohaoSum);
		nmLiaohao.addv("data", nmLiaohaoSums);
		List<NutMap> nmLiaohaos = new ArrayList<>();
		nmLiaohaos.add(nmLiaohao);
		NutMap nmTempputStateSum = NutMap.NEW().addv("name", liaohaoNum);
		nmTempputStateSum.addv("style", "background: #f59393;color:#333;font-weight:bold;");
		nmTempputStateSum.addv("data", nmLiaohaos);
		
		NutMap nmTempputeState = NutMap.NEW().addv("name", tempputState);
		nmTempputeState.addv("style", "background: #c8b9d6;color:#333;font-weight:bold;");
		List<NutMap> nmTempputStateSums = new ArrayList<>();
		nmTempputStateSums.add(nmTempputStateSum);
		nmTempputeState.addv("data", nmTempputStateSums);
		
		NutMap nmCum = NutMap.NEW().addv("name", liaohaoNum);
		nmCum.addv("style", "background: #ffbf5b;color:#333;font-weight:bold;");
		List<NutMap> nmTempputeStates = new ArrayList<>();
		nmTempputeStates.add(nmTempputeState);
		nmCum.addv("data", nmTempputeStates);
		
		NutMap nmGjehState = NutMap.NEW().addv("name", gjehState);
		nmGjehState.addv("style", "background: #e0b5bf;color:#333;font-weight:bold;");
		List<NutMap> nmCums = new ArrayList<>();
		nmCums.add(nmCum);
		nmGjehState.addv("data", nmCums);
		
		NutMap nmItem = NutMap.NEW().addv("name", item);
		nmItem.addv("style", "background: #92CDDC;color:#333;font-weight:bold;");
		List<NutMap> nmGjehStates = new ArrayList<>();
		nmGjehStates.add(nmGjehState);
		nmItem.addv("data", nmGjehStates);
		
		NutMap nmMachine = NutMap.NEW().addv("name", machine);
		nmMachine.addv("style", "background: #9bd8d9;color:#333;font-weight:bold;");
		List<NutMap> nmItems = new ArrayList<>();
		nmItems.add(nmItem);
		nmMachine.addv("data", nmItems);
		
		return nmMachine;
	}
	
	public List<NutMap> list3(){
		List<DataDetailsAuto> machines = dao.query(DataDetailsAuto.class, Cnd.NEW().groupBy("dataMachine"), null, "dataMachine");
		List<NutMap> nms = new ArrayList<>();
		int totalU = 0, totalB = 0, totalQ = 0;
		for(DataDetailsAuto daName : machines){
			NutMap nm = NutMap.NEW().addv("machine", daName.getDataMachine());
			int U = dao.count(DataDetailsAuto.class
					, Cnd.where("dataMachine", "=", daName.getDataMachine())
					.and("dataGJEHState", "=", "U"));
			totalU += U;
			nm.addv("U", U);
			int B = dao.count(DataDetailsAuto.class
					, Cnd.where("dataMachine", "=", daName.getDataMachine())
					.and("dataGJEHState", "=", "B"));
			nm.addv("B", B);
			totalB += B;
			int Q = dao.count(DataDetailsAuto.class
					, Cnd.where("dataMachine", "=", daName.getDataMachine())
					.and("dataGJEHState", "=", "Q"));
			nm.addv("Q", Q);
			totalQ += Q;
			nms.add(nm);
		}
		NutMap nmTotal = NutMap.NEW().addv("machine", "All");
		nmTotal.addv("U", totalU);
		nmTotal.addv("B", totalB);
		nmTotal.addv("Q", totalQ);
		nms.add(nmTotal);
		return nms;
	}
	public NutMap list3_1(String machine, String gjehState){
		NutMap nm = NutMap.NEW();
		if("All".equals(machine)){
			machine = "%%";
		}
		for(int i = 1; i <= 4; i ++){
			NutMap gjNm = NutMap.NEW();
			int total = dao.count(DataDetailsAuto.class
					, Cnd.where("dataMachine", "like", machine)
					.and("dataGJEHState", "=", gjehState)
					.and("gjNo", "=", "D0" + i));
			gjNm.addv("total", total);
			List<DataDetailsAuto> daChuweilists = dao.query(DataDetailsAuto.class
					, Cnd.where("dataMachine", "like", machine)
					.and("dataGJEHState", "=", gjehState)
					.and("gjNo", "=", "D0" + i).groupBy("daChuwei"), null, "daChuwei");
			List<NutMap> daChuweiNms = new ArrayList<>();
			for(DataDetailsAuto daChuwei : daChuweilists){
				NutMap daChuweiNm = NutMap.NEW().addv("dachuwei", daChuwei.getDaChuwei());
				int daChuweiSum = dao.count(DataDetailsAuto.class
						, Cnd.where("dataMachine", "like", machine)
						.and("dataGJEHState", "=", gjehState)
						.and("gjNo", "=", "D0" + i)
						.and("daChuwei", "=", daChuwei.getDaChuwei()));
				daChuweiNm.addv("sum", daChuweiSum);
				if(i == 1){
					List<DataDetailsAuto> xiaoChuweilists = dao.query(DataDetailsAuto.class
							, Cnd.where("dataMachine", "like", machine)
							.and("dataGJEHState", "=", gjehState)
							.and("gjNo", "=", "D0" + i)
							.and("daChuwei", "=", daChuwei.getDaChuwei()).groupBy("xiaoChuwei"), null, "xiaoChuwei");
					List<NutMap> xiaoChuweiNms = new ArrayList<>();
					for(DataDetailsAuto xiaoChuwei: xiaoChuweilists){
						NutMap xiaoChuweiNm = NutMap.NEW().addv("xiaoChuwei", xiaoChuwei.getXiaoChuwei());
						int xiaoChuweiSum = dao.count(DataDetailsAuto.class
								, Cnd.where("dataMachine", "like", machine)
								.and("dataGJEHState", "=", gjehState)
								.and("gjNo", "=", "D0" + i)
								.and("daChuwei", "=", daChuwei.getDaChuwei())
								.and("xiaoChuwei", "=", xiaoChuwei.getXiaoChuwei()));
						xiaoChuweiNm.addv("sum", xiaoChuweiSum);
						xiaoChuweiNms.add(xiaoChuweiNm);
					}
					daChuweiNm.addv("xiaochuwei", xiaoChuweiNms);
					
				}
				daChuweiNms.add(daChuweiNm);
			}
			gjNm.addv("data", daChuweiNms);
			nm.addv("D0" + i, gjNm);
		}
		return nm;
	}
	
	/**
	 * 根据机种ID获取数据库数据，然后转换为级联数据列表，包括样式
	 * @param machineId
	 * @return
	 */
	public NutMap xjxc(String machine, String item, String gjehState, String tempputState
			, String liaohao){
		NutMap nmLiaohao = NutMap.NEW().addv("name", liaohao);
		nmLiaohao.addv("style", "background-color: #92CDDC;color:#333;");
		List<String> gjNos = findGjNo(machine, item, gjehState, tempputState, liaohao);
		List<NutMap> nmGjNos = new ArrayList<>();
		if(gjNos == null || gjNos.size() <= 0){
			return null;
		}
		for(int q = 0; q < gjNos.size(); q ++){
			NutMap nmGjNo = NutMap.NEW().addv("name", gjNos.get(q));
			nmGjNo.addv("style", "background: #e0b5bf;color:#333;font-weight:bold;");
			List<String> dachuweis = findDachuwei(machine, item, gjehState, tempputState, liaohao, gjNos.get(q));
			List<NutMap> nmDachuweis = new ArrayList();
			for(int v = 0; v < dachuweis.size(); v ++){
				NutMap nmDachuwei = NutMap.NEW().addv("name", dachuweis.get(v));
				nmDachuwei.addv("style", "background: #c8b9d6;color:#333;font-weight:bold;");
				List<String> xiaochuweis = findXiaochuwei(machine, item, gjehState, tempputState, liaohao, gjNos.get(q), dachuweis.get(v));
				List<NutMap> nmXiaochuweis = new ArrayList<>();
				for(int b = 0; b < xiaochuweis.size(); b ++){
					NutMap nmXiaochuwei = NutMap.NEW().addv("name", xiaochuweis.get(b));
					nmXiaochuwei.addv("style", "background-color: #92CDDC;color:#333;");
					
					List<Integer> xianghaos = findXiangHao(machine, item, gjehState, tempputState, liaohao, gjNos.get(q), dachuweis.get(v), xiaochuweis.get(b));
					List<NutMap> nmXianghaos = new ArrayList<>();
					for(int x = 0; x < xianghaos.size(); x ++){
						NutMap nmXianghao = NutMap.NEW().addv("name", "#" + String.format("%05d", xianghaos.get(x)));
						nmXianghao.addv("style", "background-color: #92CDDC;color:#333;");
						List<String> sns = findSN(machine, item, gjehState, tempputState, liaohao, gjNos.get(q), dachuweis.get(v), xiaochuweis.get(b), xianghaos.get(x));
						List<NutMap> nmSns = new ArrayList<>();
						for(int s = 0; s < sns.size(); s ++){
							NutMap nmSN = NutMap.NEW().addv("name", sns.get(s));
							nmSN.addv("style", "background-color: #92CDDC;color:#333;");
							
							List<NutMap> nmInputTimes = new ArrayList<>();
							NutMap nmInputTime = NutMap.NEW();
							nmInputTime.addv("name", dao.fetch(DataDetailsAuto.class, sns.get(s)).getCreateTime());
							nmInputTime.addv("style", "background-color: #92CDDC;color:#333;");
							nmInputTimes.add(nmInputTime);
							nmSN.addv("data", nmInputTimes);
							nmSns.add(nmSN);
						}
						nmXianghao.addv("data", nmSns);
						nmXianghaos.add(nmXianghao);
					}
					nmXiaochuwei.addv("data", nmXianghaos);
					nmXiaochuweis.add(nmXiaochuwei);
				}
				
				nmDachuwei.addv("data", nmXiaochuweis);
				nmDachuweis.add(nmDachuwei);
			}
			nmGjNo.addv("data", nmDachuweis);
			nmGjNos.add(nmGjNo);
		}
	
		nmLiaohao.addv("data", nmGjNos);
		
		return nmLiaohao;
	}
	
	public int tongji(DataSummary summary, String table){
		DataMachine machine = dao.fetch(DataMachine.class, summary.getDataMachineId());
		DataItem item = dao.fetch(DataItem.class, summary.getDataItemId());
		DataGJEHState state = dao.fetch(DataGJEHState.class, summary.getDataGJEHStateId());
		DataTempputState tempput = dao.fetch(DataTempputState.class, summary.getDataTempputStateId());
		int count = 0;
		if("tb_data_1fgjeh_details_auto".equals(table)){
			count = dao.count(table
					, Cnd.where("dataMachine", "=", machine.getName())
						.and("dataItem", "=", item.getName())
						.and("dataGJEHState", "=", state.getName())
						.and("dataTempputState", "=", tempput.getName()));
		}else if("tb_data_1fgjeh_details_hm".equals(table)){
			List<DataDetailsHM> hms = dao.query(DataDetailsHM.class
					, Cnd.where("dataMachine", "=", machine.getName())
						.and("dataItem", "=", item.getName())
						.and("dataGJEHState", "=", state.getName())
						.and("dataTempputState", "=", tempput.getName()));
			if(hms != null){
				count = 0;
				for(DataDetailsHM hm : hms){
					count += (int)hm.getNum();
				}
			}
		}
		
		return count;
	}
	public int tongji(String table, String machine, String item, String gjehState, String tempputState
			, String liaohao, String gjNo, String dachuwei, String xiaochuwei){
		int count = 0;
		if("tb_data_1fgjeh_details_auto".equals(table)){
			count = dao.count(table
					, Cnd.where("dataMachine", "=", machine)
						.and("dataItem", "=", item)
						.and("dataGJEHState", "=", gjehState)
						.and("dataTempputState", "=", tempputState)
						.and("liaohao", "=", liaohao)
						.and("gjNo", "=", gjNo)
						.and("dachuwei", "=", dachuwei)
						.and("xiaochuwei", "=", xiaochuwei));
			
		}else if("tb_data_1fgjeh_details_hm".equals(table)){
			List<DataDetailsHM> hms = dao.query(DataDetailsHM.class
					, Cnd.where("dataMachine", "=", machine)
					.and("dataItem", "=", item)
					.and("dataGJEHState", "=", gjehState)
					.and("dataTempputState", "=", tempputState)
					.and("liaohao", "=", liaohao)
					.and("gjNo", "=", gjNo)
					.and("dachuwei", "=", dachuwei)
					.and("xiaochuwei", "=", xiaochuwei));
			if(hms != null){
				count = 0;
				for(DataDetailsHM hm : hms){
					count += (int)hm.getNum();
				}
			}
		}
		
		return count;
	}
	/**
	 * 更具机种ID，项目类型ID， 状态ID和暂放状态ID删除E化数据，同时也删除对应的权限，两个操作是事务相关的
	 * @param machineId
	 * @param itemId
	 * @param gjehStateId
	 * @param tempputStateId
	 * @return
	 */
	public boolean delete(int machineId, int itemId, int gjehStateId, int tempputStateId){
		DataSummary summary = dao.fetchx(DataSummary.class, machineId, itemId, gjehStateId, tempputStateId);
		if(summary != null){
			Trans.exec(new Atom() {
				@Override
				public void run() {
					// TODO Auto-generated method stub
					permissionService.delete(summary.getPermissionId());
					dao.delete(summary);
				}
			});
		}
		
		return true;
	}
	/**
	 * 添加数据E化数据对应表，同时以其名称添加一条权限到权限表
	 * @param data
	 * @return
	 */
	public boolean add(DataSummary data){
		data.setCreateTime(new Date());
		data.setUpdateTime(new Date());
		data.setInfo("");
		DataMachine machine = dao.fetch(DataMachine.class, data.getDataMachineId());
		DataItem item = dao.fetch(DataItem.class, data.getDataItemId());
		DataGJEHState gjehState = dao.fetch(DataGJEHState.class, data.getDataGJEHStateId());
		DataTempputState tempputState = dao.fetch(DataTempputState.class, data.getDataTempputStateId());
		Permission p = new Permission();
		p.setPermission("1FGjehData:add"
				+ "[Machine:"+data.getDataMachineId()
				+", item: "+data.getDataItemId()
				+",gjehState:"+data.getDataGJEHStateId()
				+", tempputState:"+data.getDataTempputStateId()+"]");
		p.setDescript("1F钢架E化  [" + machine.getName() + "->" + item.getName() + "->" + gjehState.getName() + "->" + tempputState.getName() + "] 数据添加权限");
		p.setAvaliable(true);
		p.setCreateTime(new Date());
		p.setUpdateTime(new Date());
		Trans.exec(new Atom(){
			@Override
			public void run() {
				Permission p1 = dao.insert(p);
				data.setPermissionId(p1.getId());
				dao.insert(data);
			}
		});
		
		return true;
	}
	/**
	 * 更新数据
	 * @param data
	 * @param user
	 * @return
	 */
	public Res update(DataSummary data, User user){
		DataSummary inData = dao.fetchx(DataSummary.class, data.getDataMachineId(), data.getDataItemId(), data.getDataGJEHStateId(), data.getDataTempputStateId());
		inData.setUpdateTime(new Date());
		inData.setNum(inData.getNum() + data.getNum());
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-mm-dd HH:ss:mm");
		
		inData.setInfo(inData.getInfo() == null ? "" : inData.getInfo()
				+ "<br>[" + user.getNo() + " 于 " 
				+ sf.format(inData.getUpdateTime()) 
				+ (data.getNum() < 0 ? "Out":" In ") + data.getNum() +"数据, "
				+ "当前数据为: "+inData.getNum()+"]");
		try{
			if(dao.updateIgnoreNull(inData) > 0){
				switch(new Random().nextInt(5)){
					case 0:
						return Res.NEW().code(Res.SUCCESS).msg("哈哈哈哈，提交的数据成功了");
					case 1:
						return Res.NEW().code(Res.SUCCESS).msg("好厉害，提交的数据成功了");
					case 2:
						return Res.NEW().code(Res.SUCCESS).msg("提交的数据成功了");
					case 3:
						return Res.NEW().code(Res.SUCCESS).msg("数据居然提交成功了");
					case 4:
						return Res.NEW().code(Res.SUCCESS).msg("我左看右看，上看瞎看，数据已提交成功");
					default:
						return Res.NEW().code(Res.SUCCESS).msg("呀呀呀，数据还是提交成功了");
				}
			}else{
				return Res.NEW().code(Res.ERROR).msg("额。。。数据提交失败了，再试试呗");
			}
		}catch(Exception e){
			return Res.NEW().code(Res.ERROR).msg("系统抽风了，重新在试下哈，还不行的话退出在登陆试试");
		}
	}
	/**
	 * 从钢架E化数据表中获取所有的不重复机种ID
	 * @return
	 */
	public List<Integer> findMachineIds(){
		Sql sql = Sqls.create("SELECT dataMachineId FROM tb_data_1fgjeh_summary GROUP BY dataMachineId");
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<Integer> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getInt("dataMachineId"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(Integer.class);
	}
	
	
	/**
	 * 从钢架E化数据表中获取所有的不重复机种对象
	 * @return
	 */
	public List<DataMachine> findMachines(){
		List<DataMachine> machines = new ArrayList<>();
		List<Integer> ids = findMachineIds();
		for(int id : ids){
			machines.add(dao.fetch(DataMachine.class, id));
		}
		return machines;
	}
	
	/**
	 * 
	 * @return
	 */
	public List<DataMachine> findMachinesByHM(){
		
		Sql sql = Sqls.create("SELECT dataMachine FROM tb_data_1fgjeh_details_hm where id > 0  GROUP BY dataMachine");
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("dataMachine"));
				}
				return list;
			}
		});
		dao.execute(sql);

		List<String> names = sql.getList(String.class);
		List<DataMachine> machines = new ArrayList<>();
		for(String name : names){
			List<DataMachine> ms = dao.query(DataMachine.class, Cnd.where("name", "=", name));
			if(ms != null && ms.size() > 0){
				machines.addAll(ms);
			}
		}
		return machines;
	}
	/**
	 * 
	 * @return
	 */
	public List<DataItem> findItemsByHM(){
		
		Sql sql = Sqls.create("SELECT dataItem FROM tb_data_1fgjeh_details_hm where id > 0  GROUP BY dataItem");
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("dataItem"));
				}
				return list;
			}
		});
		dao.execute(sql);

		List<String> names = sql.getList(String.class);
		List<DataItem> lists = new ArrayList<>();
		for(String name : names){
			List<DataItem> ms = dao.query(DataItem.class, Cnd.where("name", "=", name));
			if(ms != null && ms.size() > 0){
				lists.addAll(ms);
			}
		}
		return lists;
	}
	/**
	 * 
	 * @return
	 */
	public List<DataGJEHState> findGjehStatesByHM(){
		
		Sql sql = Sqls.create("SELECT dataGJEHState FROM tb_data_1fgjeh_details_hm where id > 0  GROUP BY dataGJEHState");
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("dataGJEHState"));
				}
				return list;
			}
		});
		dao.execute(sql);

		List<String> names = sql.getList(String.class);
		List<DataGJEHState> lists = new ArrayList<>();
		for(String name : names){
			List<DataGJEHState> ms = dao.query(DataGJEHState.class, Cnd.where("name", "=", name));
			if(ms != null && ms.size() > 0){
				lists.addAll(ms);
			}
		}
		return lists;
	}
	/**
	 * 
	 * @return
	 */
	public List<String> findLiaohaoByHM(){
		
		Sql sql = Sqls.create("SELECT liaohao FROM tb_data_1fgjeh_details_hm where id > 0  GROUP BY liaohao");
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("liaohao"));
				}
				return list;
			}
		});
		dao.execute(sql);

		return sql.getList(String.class);
	}
	/**
	 * 
	 * @return
	 */
	public List<DataTempputState> findTempputesByHM(){
		
		Sql sql = Sqls.create("SELECT dataTempputState FROM tb_data_1fgjeh_details_hm where id > 0  GROUP BY dataTempputState");
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("dataTempputState"));
				}
				return list;
			}
		});
		dao.execute(sql);

		List<String> names = sql.getList(String.class);
		List<DataTempputState> lists = new ArrayList<>();
		for(String name : names){
			List<DataTempputState> ms = dao.query(DataTempputState.class, Cnd.where("name", "=", name));
			if(ms != null && ms.size() > 0){
				lists.addAll(ms);
			}
		}
		return lists;
	}
	
	/**
	 * 
	 * @return
	 */
	public List<DataMachine> findMachinesByAuto(){
		
		Sql sql = Sqls.create("SELECT dataMachine FROM tb_data_1fgjeh_details_auto where id > 0  GROUP BY dataMachine");
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("dataMachine"));
				}
				return list;
			}
		});
		dao.execute(sql);

		List<String> names = sql.getList(String.class);
		List<DataMachine> machines = new ArrayList<>();
		for(String name : names){
			List<DataMachine> ms = dao.query(DataMachine.class, Cnd.where("name", "=", name));
			if(ms != null && ms.size() > 0){
				machines.addAll(ms);
			}
		}
		return machines;
	}
	/**
	 * 
	 * @return
	 */
	public List<DataItem> findItemsByAuto(String dataMachine){
		
		Sql sql = Sqls.create("SELECT dataItem FROM tb_data_1fgjeh_details_auto where dataMachine like @dataMachine  GROUP BY dataItem");
		sql.params().set("dataMachine", "%" + dataMachine + "%");
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("dataItem"));
				}
				return list;
			}
		});
		dao.execute(sql);

		List<String> names = sql.getList(String.class);
		List<DataItem> lists = new ArrayList<>();
		for(String name : names){
			List<DataItem> ms = dao.query(DataItem.class, Cnd.where("name", "=", name));
			if(ms != null && ms.size() > 0){
				lists.addAll(ms);
			}
		}
		return lists;
	}
	/**
	 * 
	 * @return
	 */
	public List<DataGJEHState> findGjehStatesByAuto(String dataMachine, String dataItem){
		
		Sql sql = Sqls.create("SELECT dataGJEHState FROM tb_data_1fgjeh_details_auto where dataMachine like @dataMachine and dataItem like @dataItem  GROUP BY dataGJEHState");
		sql.params().set("dataMachine", "%" + dataMachine + "%").set("dataItem", "%" +  dataItem + "%");
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("dataGJEHState"));
				}
				return list;
			}
		});
		dao.execute(sql);

		List<String> names = sql.getList(String.class);
		List<DataGJEHState> lists = new ArrayList<>();
		for(String name : names){
			List<DataGJEHState> ms = dao.query(DataGJEHState.class, Cnd.where("name", "=", name));
			if(ms != null && ms.size() > 0){
				lists.addAll(ms);
			}
		}
		return lists;
	}
	/**
	 * 
	 * @return
	 */
	public List<String> findLiaohaoByAuto(String dataMachine, String dataItem, String dataGJEHState, String dataTempputState){
		
		Sql sql = Sqls.create("SELECT liaohao FROM tb_data_1fgjeh_details_auto where dataMachine like @dataMachine and dataItem like @dataItem and dataGJEHState like @dataGJEHState and dataTempputState like @dataTempputState  GROUP BY liaohao");
		sql.params().set("dataMachine", "%" + dataMachine + "%").set("dataItem", "%" + dataItem + "%")
			.set("dataGJEHState", "%" + dataGJEHState + "%").set("dataTempputState", "%" + dataTempputState + "%");
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("liaohao"));
				}
				return list;
			}
		});
		dao.execute(sql);

		return sql.getList(String.class);
	}
	/**
	 * 
	 * @return
	 */
	public List<DataTempputState> findTempputesByAuto(String dataMachine, String dataItem, String dataGJEHState){
		
		Sql sql = Sqls.create("SELECT dataTempputState FROM tb_data_1fgjeh_details_auto where dataMachine like @dataMachine and dataItem like @dataItem and dataGJEHState like @dataGJEHState  GROUP BY dataTempputState");
		sql.params().set("dataMachine", "%" + dataMachine + "%").set("dataItem", "%" + dataItem + "%")
			.set("dataGJEHState", "%" + dataGJEHState + "%");
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("dataTempputState"));
				}
				return list;
			}
		});
		dao.execute(sql);

		List<String> names = sql.getList(String.class);
		List<DataTempputState> lists = new ArrayList<>();
		for(String name : names){
			List<DataTempputState> ms = dao.query(DataTempputState.class, Cnd.where("name", "=", name));
			if(ms != null && ms.size() > 0){
				lists.addAll(ms);
			}
		}
		return lists;
	}
	
	/**
	 * 从钢架E化数据表中更具用户获取所有的不重复机种ID
	 * @return
	 */
	public List<Integer> findMachineIds(User user){
		Sql sql = Sqls.create("SELECT dataMachineId FROM tb_data_1fgjeh_summary where permissionId in "+getStrIn(user)+" GROUP BY dataMachineId");
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<Integer> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getInt("dataMachineId"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(Integer.class);
	}
	/**
	 * 从钢架E化数据表中更具用户获取所有的不重复机种对象
	 * @return
	 */
	public List<DataMachine> findMachines(User user){
		List<DataMachine> machines = new ArrayList<>();
		List<Integer> ids = findMachineIds(user);
		for(int id : ids){
			machines.add(dao.fetch(DataMachine.class, id));
		}
		return machines;
	}
	/**
	 * 从钢架E化数据表中根据机种ID获取所有的不重复类型项目ID
	 * @param machineId
	 * @return 项目ID的list集合
	 */
	public List<Integer> findItemIds(int machineId){
		Sql sql = Sqls.create("SELECT dataItemId FROM tb_data_1fgjeh_summary  WHERE dataMachineId=@id GROUP BY dataItemId ");
		sql.params().set("id", machineId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<Integer> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getInt("dataItemId"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(Integer.class);
	}
	/**
	 * 从钢架E化数据表中根据机种ID获取所有的不重复类型项目对象
	 * @param machineId
	 * @return 项目对象的List集合
	 */
	public List<DataItem> findItems(int machineId){
		List<DataItem> items = new ArrayList<>();
		List<Integer> ids = findItemIds(machineId);
		for(int id : ids){
			items.add(dao.fetch(DataItem.class, id));
		}
		return items;
	}
	/**
	 * 从钢架E化数据表中根据机种ID， 用户获取所有的不重复类型项目ID
	 * @param machineId
	 * @param user
	 * @return 项目ID的list集合
	 */
	public List<Integer> findItemIds(int machineId, User user){
		
		Sql sql = Sqls.create("SELECT dataItemId FROM tb_data_1fgjeh_summary  WHERE dataMachineId=@id and permissionId in "+getStrIn(user)+" GROUP BY dataItemId ");
		sql.params().set("id", machineId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<Integer> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getInt("dataItemId"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(Integer.class);
	}
	
	/**
	 * 从钢架E化数据表中根据机种ID， 用户获取所有的不重复类型项目对象
	 * @param machineId
	 * @param user
	 * @return 项目对象的List集合
	 */
	public List<DataItem> findItems(int machineId, User user){
		List<DataItem> items = new ArrayList<>();
		List<Integer> ids = findItemIds(machineId, user);
		for(int id : ids){
			items.add(dao.fetch(DataItem.class, id));
		}
		return items;
	}
	/**
	 * 从钢架E化数据表中根据机种ID， 项目类型ID获取所有的不重复状态对象
	 * @param machineId
	 * @param itemId
	 * @return 状态对象的List集合
	 */
	public List<DataGJEHState> findGjehStates(int machineId, int itemId){
		List<DataGJEHState> gjehs = new ArrayList<>();
		List<Integer> ids = findGjehStateIds(machineId, itemId);
		for(int id : ids){
			gjehs.add(dao.fetch(DataGJEHState.class, id));
		}
		return gjehs;
	}
	/**
	 * 从钢架E化数据表中根据机种ID， 项目类型ID获取所有的不重复状态ID
	 * @param machineId
	 * @param itemId
	 * @return 状态ID的List集合
	 */
	public List<Integer> findGjehStateIds(int machineId, int itemId){
		Sql sql = Sqls.create("SELECT dataGJEHStateId FROM tb_data_1fgjeh_summary  WHERE dataMachineId=@machineId and dataItemId=@itemId GROUP BY dataGJEHStateId ");
		sql.params().set("machineId", machineId);
		sql.params().set("itemId", itemId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<Integer> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getInt("dataGJEHStateId"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(Integer.class);
	}
	/**
	 * 从钢架E化数据表中根据机种ID， 项目类型ID，用户获取所有的不重复状态ID
	 * @param machineId
	 * @param itemId
	 * @param user
	 * @return 状态ID的list集合
	 */
	public List<Integer> findGjehStateIds(int machineId, int itemId, User user){
		Sql sql = Sqls.create("SELECT dataGJEHStateId FROM tb_data_1fgjeh_summary  WHERE dataMachineId=@machineId and dataItemId=@itemId and permissionId in "+getStrIn(user)+" GROUP BY dataGJEHStateId ");
		sql.params().set("machineId", machineId);
		sql.params().set("itemId", itemId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<Integer> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getInt("dataGJEHStateId"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(Integer.class);
	}
	/**
	 * 从钢架E化数据表中根据机种ID， 项目类型ID, 用户获取所有的不重复状态对象
	 * @param machineId
	 * @param itemId
	 * @param user
	 * @return 状态对象的list集合
	 */
	public List<DataGJEHState> findGjehStates(int machineId, int itemId, User user){
		List<DataGJEHState> gjehs = new ArrayList<>();
		List<Integer> ids = findGjehStateIds(machineId, itemId, user);
		for(int id : ids){
			gjehs.add(dao.fetch(DataGJEHState.class, id));
		}
		return gjehs;
	}
	/**
	 * 据机种ID， 类型项目ID， 状态ID获取暂放状态ID
	 * @param machineId
	 * @param itemId
	 * @param gjehStateId
	 * @return 暂放状态ID的list集合
	 */
	public List<Integer> findTempputStateIds(int machineId, int itemId, int gjehStateId){
		Sql sql = Sqls.create("SELECT dataTempputStateId FROM tb_data_1fgjeh_summary  WHERE dataMachineId=@machineId and dataItemId=@itemId and dataGJEHStateId=@gjehStateId GROUP BY dataTempputStateId ");
		sql.params().set("machineId", machineId);
		sql.params().set("itemId", itemId);
		sql.params().set("gjehStateId", gjehStateId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<Integer> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getInt("dataTempputStateId"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(Integer.class);
	}
	/**
	 * 根据机种ID， 类型项目ID， 状态ID获取暂放状态 
	 * @param machineId
	 * @param itemId
	 * @param gjehStateId
	 * @return 暂放状态的list集合
	 */
	public List<DataTempputState> findTempputStates(int machineId, int itemId, int gjehStateId){
		List<DataTempputState> gjehs = new ArrayList<>();
		List<Integer> ids = findTempputStateIds(machineId, itemId, gjehStateId);
		for(int id : ids){
			gjehs.add(dao.fetch(DataTempputState.class, id));
		}
		return gjehs;
	}
	/**
	 * 根据机种ID， 类型项目ID， 状态ID， 用户获取暂放状态 ID
	 * @param machineId
	 * @param itemId
	 * @param gjehStateId
	 * @param user
	 * @return 暂放状态ID的list集合
	 */
	public List<Integer> findTempputStateIds(int machineId, int itemId, int gjehStateId, User user){
		Sql sql = Sqls.create("SELECT dataTempputStateId FROM tb_data_1fgjeh_summary  WHERE dataMachineId=@machineId and dataItemId=@itemId and dataGJEHStateId=@gjehStateId and permissionId in "+getStrIn(user)+" GROUP BY dataTempputStateId ");
		sql.params().set("machineId", machineId);
		sql.params().set("itemId", itemId);
		sql.params().set("gjehStateId", gjehStateId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<Integer> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getInt("dataTempputStateId"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(Integer.class);
	}
	
	/**
	 * 根据机种ID， 类型项目ID， 状态ID， 用户获取暂放状态 
	 * @param machineId
	 * @param itemId
	 * @param gjehStateId
	 * @param user
	 * @return 暂放状态的List集合
	 */
	public List<DataTempputState> findTempputStates(int machineId, int itemId, int gjehStateId, User user){
		List<DataTempputState> gjehs = new ArrayList<>();
		List<Integer> ids = findTempputStateIds(machineId, itemId, gjehStateId, user);
		for(int id : ids){
			gjehs.add(dao.fetch(DataTempputState.class, id));
		}
		return gjehs;
	}
	
	//======================== 我是一个安静的分割线 下面的是另外一章表  =========================================
	
	/**
	 * 根据机种ID， 类型项目ID， 状态ID， 暂放状态获取料号 
	 * @param machineId
	 * @param itemId
	 * @param gjehStateId
	 * @param tempputStateId
	 * @return 料号的List集合
	 */
	public List<String> findLiaohao(String machine, String item, String gjehState, String tempputState){
		Sql sql = Sqls.create("SELECT liaohao FROM tb_data_1fgjeh_details_auto  "
				+ "WHERE dataMachine=N'$machine' and dataItem=N'$item' and dataGJEHState=N'$gjehState'"
				+ " and dataTempputState=N'$tempputState' GROUP BY liaohao ");
		sql.vars().set("machine", machine)
			.set("item", item)
			.set("gjehState", gjehState)
			.set("tempputState", tempputState);
		
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("liaohao"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(String.class);
	}
	/**
	 * 根据机种ID， 类型项目ID， 状态ID， 暂放状态, 料号获取钢架编号 
	 * @param machineId
	 * @param itemId
	 * @param gjehStateId
	 * @param tempputStateId
	 * @param liaohao
	 * @return 钢架编号的List集合
	 */
	public List<String> findGjNo(String machine, String item, String gjehState, String tempputState
				, String liaohao){
		Sql sql = Sqls.create("SELECT gjNO FROM tb_data_1fgjeh_details_auto  "
				+ "WHERE dataMachine=N'$machine' and dataItem=N'$item' and dataGJEHState=N'$gjehState'"
				+ " and dataTempputState=N'$tempputState' and liaohao=N'$liaohao'"
				+ " GROUP BY gjNo");
		sql.vars().set("machine", machine)
			.set("item", item)
			.set("gjehState", gjehState)
			.set("tempputState", tempputState)
			.set("liaohao", liaohao);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("gjNO"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(String.class);
	}
	
	/**
	 * 根据机种ID， 类型项目ID， 状态ID， 暂放状态, 料号, 钢架编号获取大储位 
	 * @param machineId
	 * @param itemId
	 * @param gjehStateId
	 * @param tempputStateId
	 * @param liaohao
	 * @param gjNo
	 * @return 大储位的List集合
	 */
	public List<String> findDachuwei(String machine, String item, String gjehState, String tempputState
				, String liaohao, String gjNo){
		Sql sql = Sqls.create("SELECT dachuwei FROM tb_data_1fgjeh_details_auto  "
				+ "WHERE dataMachine=N'$machine' and dataItem=N'$item' and dataGJEHState=N'$gjehState'"
				+ " and dataTempputState=N'$tempputState' and liaohao=N'$liaohao'"
				+ " and gjNo=N'$gjNo' GROUP BY dachuwei");
		sql.vars().set("machine", machine)
			.set("item", item)
			.set("gjehState", gjehState)
			.set("tempputState", tempputState)
			.set("liaohao", liaohao)
			.set("gjNo", gjNo);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("dachuwei"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(String.class);
	}
	/**
	 * 根据机种ID， 类型项目ID， 状态ID， 暂放状态, 料号, 钢架编号, 大储位获取小储位 
	 * @param machineId
	 * @param itemId
	 * @param gjehStateId
	 * @param tempputStateId
	 * @param liaohao
	 * @param gjNo
	 * @param dachuwei
	 * @return 小储位的List集合
	 */
	public List<String> findXiaochuwei(String machine, String item, String gjehState, String tempputState
				, String liaohao, String gjNo, String dachuwei){
		Sql sql = Sqls.create("SELECT xiaochuwei FROM tb_data_1fgjeh_details_auto  "
				+ "WHERE dataMachine=N'$machine' and dataItem=N'$item' and dataGJEHState=N'$gjehState'"
				+ " and dataTempputState=N'$tempputState' and liaohao=N'$liaohao'"
				+ " and gjNo=N'$gjNo' and dachuwei=N'$dachuwei' GROUP BY xiaochuwei");
		sql.vars().set("machine", machine)
			.set("item", item)
			.set("gjehState", gjehState)
			.set("tempputState", tempputState)
			.set("liaohao", liaohao)
			.set("gjNo", gjNo)
			.set("dachuwei", dachuwei);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("xiaochuwei"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(String.class);
	}
	/**
	 * 根据机种ID， 类型项目ID， 状态ID， 暂放状态, 料号, 钢架编号, 大储位获,小储位获取箱号 
	 * @param machineId
	 * @param itemId
	 * @param gjehStateId
	 * @param tempputStateId
	 * @param liaohao
	 * @param gjNo
	 * @param dachuwei
	 * @return 箱号 的List集合
	 */
	public List<Integer> findXiangHao(String machine, String item, String gjehState, String tempputState
				, String liaohao, String gjNo, String dachuwei, String xiaochuwei){
		Sql sql = Sqls.create("SELECT box FROM tb_data_1fgjeh_details_auto  "
				+ "WHERE dataMachine=N'$machine' and dataItem=N'$item' and dataGJEHState=N'$gjehState'"
				+ " and dataTempputState=N'$tempputState' and liaohao=N'$liaohao'"
				+ " and gjNo=N'$gjNo' and dachuwei=N'$dachuwei' and xiaoChuwei=N'$xiaochuwei'"
				+ " and box != 0 and box=ISNULL(box, 1) GROUP BY box");
		sql.vars().set("machine", machine)
			.set("item", item)
			.set("gjehState", gjehState)
			.set("tempputState", tempputState)
			.set("liaohao", liaohao)
			.set("gjNo", gjNo)
			.set("dachuwei", dachuwei)
			.set("xiaochuwei", xiaochuwei);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<Integer> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getInt("box"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(Integer.class);
	}
	/**
	 * 根据机种ID， 类型项目ID， 状态ID， 暂放状态, 料号, 钢架编号, 大储位获,小储位,箱号SN 
	 * @param machineId
	 * @param itemId
	 * @param gjehStateId
	 * @param tempputStateId
	 * @param liaohao
	 * @param gjNo
	 * @param dachuwei
	 * @return SN的List集合
	 */
	public List<String> findSN(String machine, String item, String gjehState, String tempputState
				, String liaohao, String gjNo, String dachuwei, String xiaochuwei, int box){
		Sql sql = Sqls.create("SELECT sn FROM tb_data_1fgjeh_details_auto  "
				+ "WHERE dataMachine=N'$machine' and dataItem=N'$item' and dataGJEHState=N'$gjehState'"
				+ " and dataTempputState=N'$tempputState' and liaohao=N'$liaohao'"
				+ " and gjNo=N'$gjNo' and dachuwei=N'$dachuwei' and xiaoChuwei=N'$xiaochuwei' "
				+ " and box=$box order by createTime asc");
		sql.vars().set("machine", machine)
			.set("item", item)
			.set("gjehState", gjehState)
			.set("tempputState", tempputState)
			.set("liaohao", liaohao)
			.set("gjNo", gjNo)
			.set("dachuwei", dachuwei)
			.set("xiaochuwei", xiaochuwei)
			.set("box", box);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				// TODO Auto-generated method stub
				List<String> list = new ArrayList<>();
				while(rs.next()){
					list.add(rs.getString("sn"));
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(String.class);
	}
	/**
	 * 获取该用户的所有权限ID转为SQL IN 的字符串
	 * @param user
	 * @return  (id, id, id...)这样的字符串
	 */
	private String getStrIn(User user){
		Set<Permission> perissions = userService.findPermissionByNo(user.getNo());
		String strIn = "(";
		Iterator<Permission> it = perissions.iterator();
		while(it.hasNext()){
			Permission p = it.next();
			strIn += p.getId() + ",";
		}
		strIn = strIn.substring(0, strIn.length() - 1) + ")";
		return strIn;
	}
}
